/**
 * 
 */
package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Properties;
import java.util.logging.Level;

import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MLetterOfAttorney;
import org.compiere.model.MLetterOfAttorneyLine;
import org.compiere.model.MOrg;
import org.compiere.model.MUOM;
import org.compiere.model.Mhrmidcard;
import org.compiere.process.ProcessInfo;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.MHREmployee;
import org.utils.AnalogConvertor_RU;

import extend.org.compiere.utils.Util;

/**
 * @author A.Kulantayev
 *
 */
public class MM_Form_D1 extends SvrProcess {
	//MM_Form_D1_
	/** Current context */
	private Properties m_ctx;
	private ProcessInfo pi;	
	/**Current Record_ID */
	private int p_Record_ID = 0;
	/** */
	String file;
	@Override
	protected void prepare() {
		log.info("");
		m_ctx = Env.getCtx();
		pi = getProcessInfo();
		p_Record_ID = pi.getRecord_ID();
		if(p_Record_ID==0)
			log.log(Level.SEVERE, "Error initializing the parameter Record_ID: p_Record_ID ="+p_Record_ID);
	}


	@Override
	protected String doIt() throws Exception {
		file = Util.localFilePath + "MM_Form_D1_.xls";
		MAttachmentEntry entry = Util.getAttachment(pi, m_ctx, file);
		if (entry == null)
			throw new AdempiereException(Msg.translate(m_ctx,"NotFoundTemplate"));

		HSSFWorkbook wb = new HSSFWorkbook(entry.getInputStream());
		HSSFSheet sheet = wb.getSheetAt(0);
		Row R = null;
		//Cell C = null;
		//
		MLetterOfAttorney letterOfAttorney = new MLetterOfAttorney(m_ctx, p_Record_ID, get_TrxName());
		String sql ="";
		//
		
		//Report Header
		R = sheet.getRow(7);
		MOrg org = new MOrg(m_ctx, letterOfAttorney.getAD_Org_ID(), get_TrxName());
		MBPartner consumer = new MBPartner(m_ctx, org.getLinkedC_BPartner_ID(get_TrxName()), get_TrxName());
		R.getCell(21).setCellValue(org.getName());
		R.getCell(58).setCellValue(consumer.getFI_BIN_IIN());
		//ValidTo info
		R = sheet.getRow(9);
		R.getCell(14).setCellValue(new SimpleDateFormat("dd").format(letterOfAttorney.getValidTo()));
		R.getCell(18).setCellValue(new SimpleDateFormat("MMMMM").format(letterOfAttorney.getValidTo()));
		R.getCell(23).setCellValue(new SimpleDateFormat("yyyy").format(letterOfAttorney.getValidTo()));
		//
		//Consumer and Payer Info
		sheet.getRow(11).getCell(32).setCellValue(consumer.getName()+", "+getBPInfo(consumer.getC_BPartner_ID(), Env.getAD_Language(m_ctx), true, true));
		sheet.getRow(13).getCell(32).setCellValue(letterOfAttorney.getVendor().getName()+", "+getBPInfo(letterOfAttorney.getVendor_ID(), Env.getAD_Language(m_ctx), true, true));
		//
		//BankAccount and Name Info
		R = sheet.getRow(16);
		R.getCell(4).setCellValue(letterOfAttorney.getC_BankAccount().getAccountNo());
		R.getCell(24).setCellValue(letterOfAttorney.getC_BankAccount().getC_Bank().getName());
		//DocumentNo
		sheet.getRow(19).getCell(37).setCellValue(letterOfAttorney.getDocumentNo());
		//
		//DateIssue
		R = sheet.getRow(20);
		R.getCell(28).setCellValue(new SimpleDateFormat("dd").format(letterOfAttorney.getdateissue()));
		R.getCell(32).setCellValue(new SimpleDateFormat("MMMMM").format(letterOfAttorney.getdateissue()));
		R.getCell(37).setCellValue(new SimpleDateFormat("yyyy").format(letterOfAttorney.getdateissue()));
		//
		//Выдана
		String str="";
		sql = "SELECT HR_Employee_ID FROM HR_Employee WHERE IsActive = 'Y' AND C_BPartner_ID = "+letterOfAttorney.getC_BPartner_ID();
		int hr_Employee_ID = 0;
		MHREmployee employee = null;
		hr_Employee_ID = DB.getSQLValue(get_TrxName(), sql);
		if(hr_Employee_ID!=0)
			employee = new MHREmployee(m_ctx, hr_Employee_ID, get_TrxName());
		else
			 throw new Exception("BPartner = "+letterOfAttorney.getC_BPartner().getName()+" is not employee!");
		if(employee!=null)
			str+=employee.getHR_Job().getName()+", ";
		str+=employee.getHR_Department().getName()+", ";
		str += letterOfAttorney.getC_BPartner().getLastName()+" "+letterOfAttorney.getC_BPartner().getFirstName()+" "+letterOfAttorney.getC_BPartner().getMiddleName();
		
		sheet.getRow(22).getCell(32).setCellValue(str);
		//
		//ID_Card
		Mhrmidcard mhrmidcard = null;
		int hrm_IDCard_ID = 0;
		sql = "SELECT HRM_IDCard_ID FROM HRM_IDCard WHERE HRM_Doctype='2' AND C_BPartner_ID = "+letterOfAttorney.getC_BPartner_ID();
		hrm_IDCard_ID = DB.getSQLValue(get_TrxName(), sql);
		if(hrm_IDCard_ID!=0)
			mhrmidcard = new Mhrmidcard(m_ctx, hrm_IDCard_ID, get_TrxName());
		if(mhrmidcard!=null){
			R = sheet.getRow(24);
			R.getCell(25).setCellValue(mhrmidcard.gethrm_valuedoc());
			R.getCell(35).setCellValue(new SimpleDateFormat("dd").format(mhrmidcard.gethrm_dateofissue()));
			R.getCell(39).setCellValue(new SimpleDateFormat("MMMMM").format(mhrmidcard.gethrm_dateofissue()));
			R.getCell(45).setCellValue(new SimpleDateFormat("yyyy").format(mhrmidcard.gethrm_dateofissue()));
		}
		//	
		//Vendor Name
		sheet.getRow(26).getCell(32).setCellValue(letterOfAttorney.getVendor().getName());
		//
		//DocumentNo and Date
		sheet.getRow(29).getCell(32).setCellValue("№ "+letterOfAttorney.getDocumentNo()+" от "+ new SimpleDateFormat("dd.MM.yyyy").format(letterOfAttorney.getdateissue()));
		//
		MLetterOfAttorneyLine[] lines = letterOfAttorney.getLines(false);
		for(int i = 0, rowNo = 34,no=1; i < lines.length; i++, rowNo++, no++){
			sheet.shiftRows(rowNo, rowNo+4, 1);
			R = sheet.createRow(rowNo);
			for(int j=0; j<65; j++){
				R.createCell(j);
				R.getCell(j).setCellStyle(sheet.getRow(32).getCell(64).getCellStyle());
				R.getCell(0).setCellStyle(sheet.getRow(32).getCell(0).getCellStyle());
			}
			//col1
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 0, 3));
			R.getCell(0).setCellValue(String.valueOf(no));
			//col2
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 4, 37));
			R.getCell(4).setCellValue(lines[i].getM_Product().getName());
			//col3
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 38, 43));
			MUOM muom = new MUOM(m_ctx, lines[i].getC_UOM_ID(), get_TrxName());
			R.getCell(38).setCellValue(muom.get_Translation(muom.COLUMNNAME_Name, Env.getAD_Language(m_ctx)));
			//col4
			sheet.addMergedRegion(new CellRangeAddress(rowNo, rowNo, 44, 64));
			R.getCell(44).setCellValue(AnalogConvertor_RU.toString(lines[i].getQuantity()));
			
		}
		// Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(file);
		wb.write(fileOut);
		fileOut.close();
		// Open the file with the application associated
					Util.launchFile(new File(file));	
		return Msg.translate(m_ctx, "Success");
	}
		
	public String getBPInfo(int C_BPartner_ID, String AD_Language, boolean fi_bin_iin, boolean fullAddress){
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String bpFullInfo = "";
		String  sql="SELECT";
		if(fi_bin_iin||fullAddress){
		if(fi_bin_iin){
				if(fullAddress)
					sql+="\n COALESCE(C_BPartner.fi_bin_iin||', ','') ||";
				else 
					sql+="\n COALESCE(C_BPartner.fi_bin_iin,'')";
		}
		if(fullAddress){
			sql+="\n COALESCE(C_Country.Description||', ',C_Country.Name||', ') ||"+
				 "\n COALESCE(C_Location.Postal||', ','') ||"+ 
				 "\n COALESCE(C_Location.City||', ','') ||"+ 
				 "\n COALESCE(C_Location.Address1||', ','') ||"+ 
				 "\n COALESCE(C_Location.Address2||', ','') ||"+ 
				 "\n COALESCE(C_Location.Address3,'')"; 
		}
		}
		else{
			sql+=" * ";
		}
		
		sql+="\n FROM C_BPartner C_BPartner"+ 
			 "\n LEFT JOIN C_BPartner_location C_BPartner_location ON (C_BPartner_location.C_BPartner_ID = C_BPartner.C_BPartner_ID)"+ 
		     "\n LEFT JOIN C_Location C_Location ON (C_Location.C_Location_ID = C_BPartner_location.C_Location_ID)"+
		     "\n LEFT JOIN C_Country_trl C_Country ON (C_Country.C_Country_ID = C_Location.C_Country_ID)"+ 
		     "\n WHERE C_BPartner.IsActive = 'Y'"+
		     "\n AND  C_Country.AD_Language='"+AD_Language+"'"+
		     "\n AND C_BPartner.C_BPartner_ID = "+C_BPartner_ID;
		try{
			pstmt = DB.prepareStatement(sql, get_TrxName());
			rs = pstmt.executeQuery();
			while(rs.next()){
				bpFullInfo = rs.getString(1);
			}
			
		}catch(Exception e){
			log.log(Level.SEVERE, sql, e);
		}finally{
			DB.close(rs, pstmt);
			rs = null; pstmt = null;
		}
		
		return bpFullInfo;
	}

}
